<?php
class M_dashboard extends Nvl_Model{
	function __construct(){
		parent::__construct();
		$this->table_name='m_dashboard';
		$this->column_primary='dashboard_id';
		$this->column_order='';
		$this->column_unique='dashboard_nama';
		$this->column_active='dashboard_aktif';
	}
	
	function get_trend($sekolah_id, $tanggal_awal, $tanggal_akhir){
		$sql = "
			SELECT
				TO_CHAR(prestasi_tanggal, 'mm-yyyy') as bulan,
				COUNT(*) AS data
			FROM t_prestasi
			WHERE prestasi_tanggal < now()
			AND prestasi_valid = 1
		";
		if($_SESSION[SESSION_SCHOOLID] != 0){
			$sql .= " AND prestasi_sekolah_id = '". $_SESSION[SESSION_SCHOOLID] . "'";
		}
		if($tanggal_awal != '' && $tanggal_akhir != ''){
			$sql .= " AND prestasi_tanggal BETWEEN '". help_en_date($tanggal_awal) ."' AND '". help_en_date($tanggal_akhir) ."'";
		}else{
			$sql .= " AND prestasi_tanggal BETWEEN (NOW() - INTERVAL '1 YEAR') AND NOW()";
		}
		$sql .= ' GROUP BY bulan';
		$query = $this->db->query($sql);
		return json_encode($query->result());
	}
	
	function get_topschool($tanggal_awal, $tanggal_akhir){
		$sql = "
			SELECT
				prestasi_sekolah_nama AS sekolah_nama,
				COUNT(*) AS data,
				SUM(prestasi_skor) AS data2
			FROM t_prestasi
			WHERE prestasi_valid = 1
		";
		if($tanggal_awal != '' && $tanggal_akhir != ''){
			$sql .= " AND prestasi_tanggal BETWEEN '". help_en_date($tanggal_awal) ."' AND '". help_en_date($tanggal_akhir) ."'";
		}else{
			$sql .= " AND prestasi_tanggal BETWEEN (NOW() - INTERVAL '1 YEAR') AND NOW()";
		}
		$sql .= " GROUP BY prestasi_sekolah_nama ORDER BY data DESC LIMIT 10";
		$query = $this->db->query($sql);
		return json_encode($query->result());
	}
	
	function get_awarded_school($tanggal_awal, $tanggal_akhir, $start, $end, $search_text){
		$this->db->select('
			prestasi_sekolah_nama AS sekolah_nama,
			COUNT(*) AS total_prestasi,
			SUM(prestasi_skor) AS total_skor
		');
		$this->db->group_by('prestasi_sekolah_nama')
				->order_by('total_prestasi', 'desc')
				->where('prestasi_valid', '1');
		
		if($tanggal_awal != '' && $tanggal_akhir != ''){
			$this->db->where("prestasi_tanggal BETWEEN '". help_en_date($tanggal_awal) ."' AND '". help_en_date($tanggal_akhir) ."'", FALSE);
		}else{
			$this->db->where("prestasi_tanggal BETWEEN (NOW() - INTERVAL '1 YEAR') AND NOW()");
		}
		$query = $this->db->get('t_prestasi');
		$final[] = array(
			"total"=>$query->num_rows(),
			"results"=>$query->result()
		);
		return json_encode($final);
	}
	
	function get_topstudent($tanggal_awal, $tanggal_akhir){
		$sql = "
			SELECT
				det_prestasi_siswa_id AS siswa,
				det_prestasi_siswa_nama AS siswa,
				COUNT(*) AS data,
				SUM(prestasi_skor) AS data2
			FROM t_prestasi_det
			JOIN t_prestasi ON t_prestasi.prestasi_id = t_prestasi_det.det_prestasi_prestasi_id
			AND prestasi_valid = 1
		";
		if($tanggal_awal != '' && $tanggal_akhir != ''){
			$sql .= " AND prestasi_tanggal BETWEEN '". help_en_date($tanggal_awal) ."' AND '". help_en_date($tanggal_akhir) ."'";
		}else{
			$sql .= " AND prestasi_tanggal BETWEEN (NOW() - INTERVAL '1 YEAR') AND NOW()";
		}
		if($_SESSION[SESSION_SCHOOLID] != 0){
			$sql .= " AND prestasi_sekolah_id = '". $_SESSION[SESSION_SCHOOLID] . "'";
		}
		$sql .= " GROUP BY det_prestasi_siswa_id, det_prestasi_siswa_nama ORDER BY data DESC LIMIT 10";
		$query = $this->db->query($sql);
		return json_encode($query->result());
	}
	
	
	function get_awarded_student($tanggal_awal, $tanggal_akhir, $start, $end, $search_text){
		$this->db->select('
			det_prestasi_siswa_nama AS siswa_nama,
			det_prestasi_siswa_kelas AS siswa_kelas,
			COUNT(*) AS total_prestasi,
			SUM(prestasi_skor) AS total_skor
		');
		$this->db->join('t_prestasi', 't_prestasi.prestasi_id = t_prestasi_det.det_prestasi_prestasi_id')
				->group_by('det_prestasi_siswa_id, det_prestasi_siswa_nama, det_prestasi_siswa_kelas')
				->order_by('total_prestasi', 'desc')
				->where('prestasi_valid', '1');
		if($_SESSION[SESSION_SCHOOLID] != 0){
			$this->db->where('prestasi_sekolah_id', $_SESSION[SESSION_SCHOOLID]);
		}
		if($tanggal_awal != '' && $tanggal_akhir != ''){
			$this->db->where("prestasi_tanggal BETWEEN '". help_en_date($tanggal_awal) ."' AND '". help_en_date($tanggal_akhir) ."'", FALSE);
		}else{
			$this->db->where("prestasi_tanggal BETWEEN (NOW() - INTERVAL '1 YEAR') AND NOW()");
		}
		$query = $this->db->get('t_prestasi_det');
		$final[] = array(
			"total"=>$query->num_rows(),
			"results"=>$query->result()
		);
		return json_encode($final);
	}
}